import mysql.connector


# re_status 0未,1ok,9模糊,8常识
mydb = mysql.connector.connect(
    host="192.168.47.135",  # 数据库主机地址
    user="myuser",  # 数据库用户名
    port=3307,
    database="iridescent_study",  # 数据库名称 没有的话插入操作报错
    passwd="211314wccA@",  # 数据库密码
)

mycursor = mydb.cursor()

# 插入字典表
init = " SELECT CASE WHEN re_status =0 THEN '未记' WHEN re_status =1 THEN '模糊' WHEN re_status =2 THEN '熟记'  WHEN re_status =3 THEN '常用' END AS STATUS , COUNT(1) AS COUNT FROM egl_word_copy GROUP BY re_status order by re_status "
init2 = "SELECT CASE WHEN re_status =0 THEN '未记' WHEN re_status =1 THEN '模糊' WHEN re_status =2 THEN '熟记'  WHEN re_status =3 THEN '常用' END AS STATUS , COUNT(1) AS COUNT FROM egl_word_copy where group_id ={} GROUP BY re_status;"
sql = "select * from egl_word_copy where re_status ={} and group_id ={} ORDER BY RAND() LIMIT 1"
sql2 = "select * from egl_word_copy where re_status ={}  ORDER BY RAND() LIMIT 1"
delete_sql = "DELETE FROM egl_word_copy WHERE id = {} "
update_sql = "UPDATE  egl_word_copy SET re_status ={} WHERE id ={}"
group_msg = '''
SELECT t.group_id,IFNULL(a.num, 0) AS no_num,IFNULL(b.num , 0)AS mo_num ,IFNULL(c.num , 0)AS yes_num,IFNULL(d.num , 0)AS shu_num

FROM 
(SELECT DISTINCT group_id FROM egl_word_copy ) t

LEFT JOIN 
(
SELECT 
 group_id,
COUNT(1) AS num FROM egl_word_copy 
WHERE re_status =0
GROUP BY re_status,group_id) a
ON t.group_id =a.group_id
LEFT JOIN 
(
SELECT 
group_id,
COUNT(1) AS num FROM egl_word_copy 
WHERE re_status =1
GROUP BY re_status,group_id)b
ON t.group_id =b.group_id

LEFT JOIN 
(
SELECT 
 group_id,
COUNT(1) AS num FROM egl_word_copy 
WHERE re_status =2
GROUP BY re_status,group_id) c
ON t.group_id =c.group_id

LEFT JOIN 
(
SELECT 
 group_id,
COUNT(1) AS num FROM egl_word_copy 
WHERE re_status =3
GROUP BY re_status,group_id) d
ON t.group_id =d.group_id

 ORDER BY group_id ASC
 '''



def get_group_msg():
    mycursor.execute(group_msg)
    results = mycursor.fetchall()
    count = 0
    for row in results:
        row_str = row[0].__str__()
        if len(row_str) != 2:
            row_str = "0" + row_str
        row1_str = row[1].__str__()
        while len(row1_str) != 3:
            row1_str = "0" + row1_str
        row2_str = row[2].__str__()
        while len(row2_str) != 3:
            row2_str = "0" + row2_str
        row3_str = row[3].__str__()
        while len(row3_str) != 3:
            row3_str = "0" + row3_str
        row4_str = row[4].__str__()
        while len(row4_str) != 3:
            row4_str = "0" + row4_str
        print_str = row_str + "组,未:" + row1_str + ",模:" + row2_str+ ",熟:" + row3_str+ ",常:" + row4_str + "|";
        if count == 6:
            print(print_str)
            count = 0
        print(print_str, end="")
        count = count + 1;
    print()


def delete_word(id):
    tmp_sql = delete_sql.format(id)
    mycursor.execute(tmp_sql)
    mydb.commit()


def get_msg(group_id):
    tmp_sql = init2.format(group_id)
    mycursor.execute(tmp_sql)
    results = mycursor.fetchall()
    results_str = "";
    for row in results:
        results_str = results_str + row[0] + ":" + row[1].__str__() + "个, "
    print(results_str)


def get_msg2():
    mycursor.execute(init)
    results = mycursor.fetchall()
    results_str = "";
    for row in results:
        results_str = results_str + row[0] + ":" + row[1].__str__() + "个, "
    print(results_str)


def get_date(data_type, group_id):
    tmp_sql = sql.format(data_type, group_id)
    mycursor.execute(tmp_sql)
    results = mycursor.fetchall()
    if results:
        return results[0]
    else:
        exit("no data")


def get_date2(data_type):
    tmp_sql = sql2.format(data_type, group_id)
    mycursor.execute(tmp_sql)
    results = mycursor.fetchall()
    return results[0]


def update_status(id, status):
    tmp_sql = update_sql.format(status, id)
    # print(tmp_sql)
    mycursor.execute(tmp_sql)
    mydb.commit()


def group_word(size):
    print("分组size为: " + size.__str__())
    for i in range(int(6000 / int(size))):
        # if i == 0:
        #     #continue
        start_num = i * int(size)
        #print(start_num)
        group_sql = "UPDATE  egl_word_copy SET group_id =" + (i+1).__str__() + " WHERE id in ( select id from ( SELECT id from egl_word_copy order by id asc limit " + start_num.__str__() + "," + size + ") a )"
        #print(group_sql)
        mycursor.execute(group_sql)
        mydb.commit()


if __name__ == '__main__':
    get_group_msg()
    print("选择分组( -g:重新分组)")
    group_id = input();
    if "-g" == group_id:
        print("选择分组size")
        group_size = input();
        group_word(group_size)
        get_group_msg()
        print("选择分组")
        group_id = input();
    if group_id:
        get_msg(group_id)
    else:
        get_msg2()
    print("选择查询类型 : 0未,1模糊,2熟记,3常用")
    select_type = input();
    # print(group_id)
    # print("选择方式 : see, hear")
    # get_type = input();
    get_type="see"
    while 1:
        word_data = {};
        if group_id:
            if not select_type:
                select_type = 0
            word_data = get_date(select_type, group_id)
        else:
            if not select_type:
                select_type = 0
            word_data = get_date2(select_type)
        print(word_data[1],end="")
        # ttx.sayText(word_data[1])
        # for char_t in word_data[1]:
        #     ttx.sayText(char_t)
        # ttx.sayText(word_data[1])
        xx=''
        if get_type=='see':
            xx = input()
        else:
            xx=''
        if xx == "exit":
            exit(0)
        print(word_data[2],end="")
        # ttx.sayText(word_data[2])
        xx2=''
        if get_type=='see':
            xx2 = input()
        else:
            xx2=''
        if xx2 == "r":
            delete_word(word_data[0])
            continue
        if xx2 == "exit":
            exit(0)
        if xx2 == "0" or xx2 =="1"  or xx2 =="2"  or xx2 =="3":
            update_status(word_data[0], xx2)

# for row in results:
#     tmp_count = tmp_count + 1
#     tmp_write_str = tmp_write_str + ", 第 " + tmp_count.__str__() + "个单词";
#     tmp_write_str = tmp_write_str + row[1];
#     tmp_write_str = tmp_write_str + ",";
#     print(tmp_write_str)
# for tmp_char in row[0]:
#     # print(tmp_char)
#     tmp_write_str = tmp_write_str + tmp_char;
#     tmp_write_str = tmp_write_str + " ";
# tmp_write_str = tmp_write_str + " ,,";
# tmp_write_str = tmp_write_str + row[0];
# tmp_write_str = tmp_write_str + " ,,";
# tmp_write_str = tmp_write_str + row[1];
